/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package DAO;

import Bean.ApplicantBean;
import Utility.DBProcess;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author Vietlt
 */
public class ApplicantDAO {

    public static void main(String[] args) {
        /*Phần này để test xem hàm LoadAllApplicant() đã lấy được dữ liệu chưa ...
         //LoadAll*/
         List<ApplicantBean> listApp = new ApplicantDAO().LoadAllApplicant();
         for (ApplicantBean apc : listApp) {
         System.out.println("ID :" + apc.getApplicantID());
         System.out.println("First Name :" + apc.getFirstName());
         System.out.println("Last Name :" + apc.getLastName());
         }
        
        
//        ApplicantBean aB = new ApplicantDAO().LoadAllApplicantById(5);
//        System.out.println("ID :" + aB.getApplicantID());
//        System.out.println("First :" + aB.getFirstName());
//        System.out.println("Last Name :" + aB.getLastName());

//        ApplicantBean ab = new ApplicantBean();
//        ab.setApplicantID(1);
//        ab.setFirstName("Lại Tiến333");
//        ab.setLastName("Việt333");
//        ab.setGender(true);
//        ab.setDob("10/10/1999");
//        ab.setPhoneNumber("0974766158");
//        ab.setEmail("laitienviet111@gmail.com");
//        ab.setAddress("Hà Nội");
//        ab.setStatus(2);
//        ab.setSalaryRequirement(9000000);
//        ab.setLanguage("Java");
//        ab.setYearOfExperience(4);
//        ab.setDegree("Việt1");
//        ab.setCreatedDate("11/11/1999");
//        ab.setSkill("Việt1");
//        ab.setAward("Việt1");
//        int n = new ApplicantDAO().UpdateApplicant(ab);
//        if (n > 0) {
//            System.out.println("ok");
//        } else {
//            System.out.println("no ok");
//        }

//        AddNew
//        ApplicantBean ab = new ApplicantBean();
//        ab.setFirstName("Lại Tiến3332");
//        ab.setLastName("Việt333");
//        ab.setGender(true);
//        ab.setDob("10/10/1999");
//        ab.setPhoneNumber("0974766158");
//        ab.setEmail("laitienviet3233@gmail.com");
//        ab.setAddress("Hà Nội");
//        ab.setStatus(2);
//        ab.setSalaryRequirement(9000000);
//        ab.setLanguage("Java");
//        ab.setYearOfExperience(4);
//        ab.setDegree("Việt1");
//        ab.setCreatedDate("11/11/1999");
//        ab.setSkill("Việt1");
//        ab.setAward("Việt1");
//        int rs = new ApplicantDAO().AddNewApplicant(ab);
//        if (rs == 1) {
//            System.out.println("Add OKK");
//        }
        
        
//          DELETE
//        int rs = new ApplicantDAO().DeleteNewApplicant(9);
//        if (rs == 1) {
//            System.out.println("Delete OKkkkkkkkkkkkkkkkkkkkK");
//        } else {
//            System.out.println("Noooooooooooooooooooooooooooooooo");
//        }
    }

    public List<ApplicantBean> LoadAllApplicant() {
        List<ApplicantBean> list = new ArrayList<ApplicantBean>();
        Connection conn = null;
        CallableStatement stm = null;
        ResultSet rs = null;
        conn = DBProcess.getConnection();
        try {
            stm = conn.prepareCall("{ call Applicant_getall}"); //Applicant_add = Ten Procedure 
            rs = stm.executeQuery();
            while (rs.next()) {
                ApplicantBean aB = new ApplicantBean();
                aB.setApplicantID(rs.getInt("ApplicantID"));
                aB.setFirstName(rs.getString("FirstName"));
                aB.setLastName(rs.getString("LastName"));
                aB.setGender(rs.getBoolean("Gender"));
                aB.setDob(rs.getDate("DOB")+"");
                aB.setPhoneNumber(rs.getString("PhoneNumber"));
                aB.setEmail(rs.getString("Email"));
                aB.setAddress(rs.getString("Address"));
                aB.setStatus(rs.getInt("Status"));
                aB.setSalaryRequirement(rs.getFloat("SalaryRequirement"));
                aB.setLanguage(rs.getString("Language"));
                aB.setYearOfExperience(rs.getInt("YearOfExperience"));
                aB.setDegree(rs.getString("Degree"));
                aB.setCreatedDate(rs.getDate("CreatedDate")+"");
                aB.setSkill(rs.getString("Skill"));
                aB.setAward(rs.getString("Award"));
                list.add(aB);
                if (list.isEmpty()) {
                    String err = "List empty !!!";
                }
            }
        } catch (SQLException ex) {
            Logger.getLogger(ApplicantDAO.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            DBProcess.closeResultSet(rs);
            DBProcess.closeStatement(stm);
            DBProcess.closeConnection(conn);
        }
        return list;
    }

    public ApplicantBean LoadAllApplicantById(int ApplicantID) {
        Connection conn = null;
        CallableStatement stm = null;
        ResultSet rs = null;
        conn = DBProcess.getConnection();
        ApplicantBean aB = new ApplicantBean();
        try {
            stm = conn.prepareCall("{ call Applicant_get (?)}"); //Applicant_add = Ten Procedure 
            stm.setInt(1, ApplicantID);
            rs = stm.executeQuery();
            while (rs.next()) {
                aB.setApplicantID(rs.getInt("ApplicantID"));
                aB.setFirstName(rs.getString("FirstName"));
                aB.setLastName(rs.getString("LastName"));
                aB.setGender(rs.getBoolean("Gender"));
                aB.setDob(rs.getDate("Dob").toString());
                aB.setPhoneNumber(rs.getString("PhoneNumber"));
                aB.setEmail(rs.getString("Email"));
                aB.setAddress(rs.getString("Address"));
                aB.setStatus(rs.getInt("Status"));
                aB.setSalaryRequirement(rs.getFloat("SalaryRequirement"));
                aB.setLanguage(rs.getString("Language"));
                aB.setYearOfExperience(rs.getInt("YearOfExperience"));
                aB.setDegree(rs.getString("Degree"));
                aB.setCreatedDate(rs.getDate("CreatedDate").toString());
                aB.setSkill(rs.getString("Skill"));
                aB.setAward(rs.getString("Award"));
            }
        } catch (SQLException ex) {
            Logger.getLogger(ApplicantDAO.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            DBProcess.closeResultSet(rs);
            DBProcess.closeStatement(stm);
            DBProcess.closeConnection(conn);
        }
        return aB;
    }

    public int AddNewApplicant(ApplicantBean aB) {
        Connection conn = null;
        CallableStatement stm = null;
        conn = DBProcess.getConnection();
        int isComplete = 0;
        try {
            String dob = aB.getDob();
            String crDate = aB.getCreatedDate();
            SimpleDateFormat format = new SimpleDateFormat("dd/MM/yyyy");
            Date dDob = format.parse(dob);
            Date dcrDate = format.parse(crDate);
            stm = conn.prepareCall("{ call Applicant_add (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)}");
            stm.setString(1, aB.getFirstName());
            stm.setString(2, aB.getLastName());
            stm.setBoolean(3, aB.isGender());
            stm.setDate(4, new java.sql.Date(dDob.getTime()));
            stm.setString(5, aB.getPhoneNumber());
            stm.setString(6, aB.getEmail());
            stm.setString(7, aB.getAddress());
            stm.setInt(8, aB.getStatus());
            stm.setFloat(9, aB.getSalaryRequirement());
            stm.setString(10, aB.getLanguage());
            stm.setInt(11, aB.getYearOfExperience());
            stm.setString(12, aB.getDegree());
            stm.setDate(13, new java.sql.Date(dcrDate.getTime()));
            stm.setString(14, aB.getSkill());
            stm.setString(15, aB.getAward());
            if (stm.executeUpdate() == 1) {
                isComplete = 1;
            } else {
                isComplete = 0;
            }
        } catch (Exception ex) {
            Logger.getLogger(ApplicantDAO.class.getName()).log(Level.SEVERE, null, ex);
        }

        return isComplete;
    }

    public int UpdateApplicant(ApplicantBean upB) {
        Connection conn = null;
        CallableStatement stm = null;
        conn = DBProcess.getConnection();
        //ApplicantBean upB=new ApplicantBean();
        int isComplete = 0;
        try {
            String dob = upB.getDob();
            String crDate = upB.getCreatedDate();
            SimpleDateFormat format = new SimpleDateFormat("dd/MM/yyyy");
            Date dDob = format.parse(dob);
            Date dcrDate = format.parse(crDate);
            stm = conn.prepareCall("{ call Applicant_update (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)}");
            stm.setInt(1, upB.getApplicantID());
            stm.setString(2, upB.getFirstName());
            stm.setString(3, upB.getLastName());
            stm.setBoolean(4, upB.isGender());
            stm.setDate(5, new java.sql.Date(dDob.getTime()));
            stm.setString(6, upB.getPhoneNumber());
            stm.setString(7, upB.getEmail());
            stm.setString(8, upB.getAddress());
            stm.setInt(9, upB.getStatus());
            stm.setFloat(10, upB.getSalaryRequirement());
            stm.setString(11, upB.getLanguage());
            stm.setInt(12, upB.getYearOfExperience());
            stm.setString(13, upB.getDegree());
            stm.setDate(14, new java.sql.Date(dcrDate.getTime()));
            stm.setString(15, upB.getSkill());
            stm.setString(16, upB.getAward());
            if (stm.executeUpdate() == 1) {
                isComplete = 1;
            } else {
                isComplete = 0;
            }
        } catch (Exception ex) {
            Logger.getLogger(ApplicantDAO.class.getName()).log(Level.SEVERE, null, ex);
        }

        return isComplete;
    }

    public int DeleteNewApplicant(int applicantID) {
        Connection conn = null;
        CallableStatement stm = null;
        conn = DBProcess.getConnection();
        int isComplete = 0;
        try {
            stm = conn.prepareCall("{ call Applicant_delete (?)}");
            stm.setInt(1, applicantID);
            if (stm.executeUpdate() == 1) {
                isComplete = 1;
            } else {
                isComplete = 0;
            }
        } catch (SQLException ex) {
            Logger.getLogger(ApplicantDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        return isComplete;
    }
}
